var myDb = require('../config/myDb');
var utils = require('../utils/utils');

function linkService() {
    var self = this;

    myDb.connect();
}

linkService.prototype.addType = function (uid, parentId, name, description, callback) {
    var self = this;

    var sqlStr = `INSERT INTO my_types (
                    ownerid,
                    parentid,
                    name,
                    description,
                    collapse,
                    token,
                    time
                )
                VALUES
                    (? ,? ,? ,?, 1 ,? ,?)`;
    myDb.executeNonQuery(sqlStr, [uid, parentId, name, description, utils.getRandomString(10), utils.dateToString(new Date())], function (err) {
        callback && callback();
    });
};

linkService.prototype.addLink = function (uid, typeId, name, url, description, callback) {
    var self = this;

    var sqlStr = `INSERT INTO my_favorites (
                    ownerid,
                    typeid,
                    name,
                    description,
                    url,
                    token,
                    time,
                    visitcount,
                    state
                )
                VALUES ( ? ,? ,? ,? ,? ,? ,?, 0, 1)`;
    myDb.executeNonQuery(sqlStr, [uid, typeId, name, description, url, utils.getRandomString(10), utils.dateToString(new Date())], function (err) {
        callback && callback();
    });
};

linkService.prototype.searchLink = function (uid, key, callback) {
    var self = this;
    var sqlStr = `SELECT
                    *
                FROM
                    my_favorites
                WHERE
                    ownerid =${uid}
                AND state = 1
                AND (name || description || url) LIKE '%${key}%'
                ORDER BY
                    visitcount DESC,
                    time DESC`;
    myDb.getRows(sqlStr, {}, function (err, rows) {
        callback && callback(rows);
    });
};

linkService.prototype.getType = function (uid, typeId, callback) {
    var self = this;
    var sqlStr = `SELECT
                    *
                FROM
                    my_types t
                WHERE
                    ownerid = ${uid}
                AND id = ${typeId}`;
    myDb.getRow(sqlStr, undefined, function (err, row) {
        callback && callback(row);
    });
};

linkService.prototype.moveType = function (uid, typeId, parentId, callback) {
    var self = this;

    var sqlStr = `UPDATE my_types
                SET parentid =${parentId}
                WHERE
                    id =${typeId}
                AND ownerid =${uid}`;
    myDb.executeNonQuery(sqlStr, {}, function (err) {
        callback && callback();
    });
};

linkService.prototype.moveLink = function (uid, linkId, typeId, callback) {
    var self = this;

    var sqlStr = `UPDATE my_favorites
                SET typeid =${typeId}
                WHERE
                    id =${linkId}
                AND ownerid =${uid}`;
    myDb.executeNonQuery(sqlStr, {}, function (err) {
        callback && callback();
    });
};

linkService.prototype.deleteLink = function (uid, linkId, callback) {
    var self = this;

    var sqlStr = `UPDATE my_favorites
                SET state =0
                WHERE
                    id =${linkId}
                AND ownerid =${uid}`;
    myDb.executeNonQuery(sqlStr, {}, function (err) {
        callback && callback();
    });
};

linkService.prototype.updateCollapse = function (uid, typeId, state, callback) {
    var self = this;

    var sqlStr = `UPDATE my_types
                SET collapse =${state}
                WHERE
                    id =${typeId}
                AND ownerid =${uid}`;
    myDb.executeNonQuery(sqlStr, {}, function (err) {
        callback && callback();
    });
};

linkService.prototype.listFrequently = function (uid, count, callback) {
    var self = this;

    var sqlStr = `SELECT
                    *
                FROM
                    my_favorites
                WHERE
                    state = 1
                AND ownerid =${uid}
                ORDER BY
                    visitcount DESC,
                    time DESC
                LIMIT 0 ,${count}`;
    myDb.getRows(sqlStr, {}, function (err, rows) {
        callback && callback(rows);
    });
};

linkService.prototype.visit = function (uid, linkId, callback) {
    var self = this;

    var sqlStr = `UPDATE my_favorites
                SET visitcount = visitcount + 1
                WHERE
                    id =${linkId}
                AND ownerid =${uid}`;
    myDb.executeNonQuery(sqlStr, {}, function (err) {
        callback && callback();
    });
};

linkService.prototype.loadLinks = function (uid, typeId, top = 36, callback) {
    var self = this;

    var sqlStr = `SELECT
                    *
                FROM
                    my_favorites
                WHERE
                    ownerid = ${uid}
                AND state = 1
                AND typeid = ${typeId}
                ORDER BY
                    visitcount DESC,
                    time DESC
                LIMIT 0,
                ${top}`;
    myDb.getRows(sqlStr, {}, function (err, rows) {
        callback && callback(rows);
    });
};

linkService.prototype.loadTypes = function (uid, parentId, callback) {
    var self = this;

    var sqlStr = `SELECT
                        t.*, f.count,
                        sub.subCount
                    FROM
                        my_types t
                    LEFT JOIN (
                        SELECT
                            typeid,
                            count(1) count
                        FROM
                            my_favorites
                        WHERE
                            state = 1
                        GROUP BY
                            typeid
                    ) f ON t.id = f.typeid
                    LEFT JOIN (
                        SELECT
                            parentid,
                            count(1) subCount
                        FROM
                            my_types t
                        WHERE
                            t.ownerid = ${uid}
                        GROUP BY
                            parentid
                    ) sub ON t.id = sub.parentid
                    WHERE
                        t.ownerid = ${uid}
                    AND t.parentid = ${parentId}`

    myDb.getRows(sqlStr, {}, function (err, rows) {
        rows = rows || [];
        rows.forEach(row => {
            row.count = row.count || 0;
            row.isParent = row.subCount > 0;
        });
        callback && callback(rows);
    });
};

module.exports = linkService;